/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tces.controller;

import com.tces.model.SubjectOffering;
import com.tces.model.SubjectSchedule;
import com.tces.utils.ConnectionDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.AbstractList;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author Arnold
 */
public class SubjectOfferingDAO extends ConnectionDAO {

    private static final Logger log = Logger.getLogger(SubjectOfferingDAO.class);

    public static List<SubjectOffering> getFullname() throws Exception {
        List<SubjectOffering> nameList = new ArrayList<SubjectOffering>();
        String sql = "SELECT\n"
                + "so.subject_offering_id,\n"
                + "CONCAT(c.college_code,' - ',so.subject_offering_name)subject_offering_name\n"
                + "FROM\n"
                + "subject_offerings AS so\n"
                + "INNER JOIN colleges AS c ON c.college_id = so.college_id\n"
                + "WHERE\n"
                + "so.subject_offering_status = 1\n"
                + "ORDER BY CONCAT(c.college_code,'-',so.subject_offering_name)";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        while (rs.next()) {
            SubjectOffering subjectOffering = new SubjectOffering();
            subjectOffering.setSubjectOfferingID(rs.getInt("subject_offering_id"));
            subjectOffering.setSubjectOfferingName(rs.getString("subject_offering_name"));
            nameList.add(subjectOffering);
            
        }
        closeConnection(getConnectionObject());
        return nameList;
    }
public static List<SubjectOffering> getFullname(int collegeID) throws Exception {
        List<SubjectOffering> nameList = new ArrayList<SubjectOffering>();
        String sql = "SELECT\n"
                + "so.subject_offering_id,\n"
                + "CONCAT(c.college_code,' - ',so.subject_offering_name)subject_offering_name\n"
                + "FROM\n"
                + "subject_offerings AS so\n"
                + "INNER JOIN colleges AS c ON c.college_id = so.college_id\n"
                + "WHERE\n"
                + "so.subject_offering_status = 1\n"
                + "AND c.college_id  = ?\n"
                + "ORDER BY CONCAT(c.college_code,'-',so.subject_offering_name)";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        preparedStatement.setInt(1, collegeID);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        while (rs.next()) {
            SubjectOffering subjectOffering = new SubjectOffering();
            subjectOffering.setSubjectOfferingID(rs.getInt("subject_offering_id"));
            subjectOffering.setSubjectOfferingName(rs.getString("subject_offering_name"));
            nameList.add(subjectOffering);
            
        }
        closeConnection(getConnectionObject());
        return nameList;
    }
    public static List<SubjectOffering> view() throws Exception {
        List<SubjectOffering> subjectOfferingList = new ArrayList<SubjectOffering>();
        String sql = "SELECT\n"
                + "c.college_id,\n"
                + "c.college_code,\n"
                + "c.college_name,\n"
                + "so.subject_offering_id,\n"
                + "so.subject_offering_name,\n"
                + "so.subejct_offering_sy_from,\n"
                + "so.subject_offering_sy_to,\n"
                + "so.subject_offering_semester,\n"
                + "so.subject_offering_status\n"
                + "FROM\n"
                + "colleges AS c\n"
                + "INNER JOIN subject_offerings AS so ON so.college_id = c.college_id";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        SubjectOffering subjectOffering;
        while (rs.next()) {
            subjectOffering = new SubjectOffering();
            subjectOffering.setSubjectOfferingID(rs.getInt("subject_offering_id"));
            subjectOffering.setCollegeID(rs.getInt("college_id"));
            subjectOffering.setSubjectOfferingSyFrom(rs.getInt("subejct_offering_sy_from"));
            subjectOffering.setSubjectOfferingSyTo(rs.getInt("subject_offering_sy_to"));
            subjectOffering.setSubjectOfferingSemester(rs.getInt("subject_offering_semester"));
            subjectOffering.setSubjectOfferingStatus(rs.getInt("subject_offering_status"));
            subjectOffering.setSubjectOfferingName(rs.getString("subject_offering_name"));
            subjectOffering.setCollegeCode(rs.getString("college_code"));
            subjectOffering.setCollegeName(rs.getString("college_name"));
            subjectOfferingList.add(subjectOffering);
        }
        closeConnection(getConnectionObject());
        return subjectOfferingList;
    }

    public static int add(SubjectOffering subjectOffering) throws Exception {
        try {
            String sql = "INSERT INTO subject_offerings(subject_offering_name,subejct_offering_sy_from,subject_offering_sy_to,subject_offering_semester,subject_offering_status,college_id) values(?,?,?,?,?,?)";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, subjectOffering.getSubjectOfferingName());
            preparedStatement.setInt(2, subjectOffering.getSubjectOfferingSyFrom());
            preparedStatement.setInt(3, subjectOffering.getSubjectOfferingSyTo());
            preparedStatement.setInt(4, subjectOffering.getSubjectOfferingSemester());
            preparedStatement.setInt(5, subjectOffering.getSubjectOfferingStatus());
            preparedStatement.setInt(6, subjectOffering.getCollegeID());
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }

    public static int update(SubjectOffering subjectOffering) throws Exception {
        try {
            String sql = "UPDATE subject_offerings SET subject_offering_name=?,subejct_offering_sy_from=?,subject_offering_sy_to=?,subject_offering_semester=?,subject_offering_status=?,college_id=? WHERE subject_offering_id=?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, subjectOffering.getSubjectOfferingName());
            preparedStatement.setInt(2, subjectOffering.getSubjectOfferingSyFrom());
            preparedStatement.setInt(3, subjectOffering.getSubjectOfferingSyTo());
            preparedStatement.setInt(4, subjectOffering.getSubjectOfferingSemester());
            preparedStatement.setInt(5, subjectOffering.getSubjectOfferingStatus());
            preparedStatement.setInt(6, subjectOffering.getCollegeID());
            preparedStatement.setInt(7, subjectOffering.getSubjectOfferingID());
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }

    public static int delete(int subjectOfferingID) throws Exception {
        try {
            String sql = "DELETE FROM subject_offerings WHERE subject_offering_id=?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, subjectOfferingID);
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }
}
